package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import model.criminalModel;
import util.dataUtil;

public class criminalDAO {
	public static ArrayList getAll( int status){
		ArrayList listcriminal = new ArrayList();
		String sql = "select cr.* from criminal_reg cr,CRIME_DETAIL cd where cr.id=cd.idcriminal and status_crime=?";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ps.setInt(1, status);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				criminalModel model = new criminalModel();
				model.setId(rs.getInt("id"));
				model.setFirstName(rs.getString("firstName"));
				model.setLastName(rs.getString("lastName"));
				model.setBirth(rs.getString("birth"));
				model.setAddress(rs.getString("address"));
				model.setSex(rs.getInt("sex"));
				model.setImage(rs.getString("image"));
				model.setIduser(rs.getInt("iduser"));
				model.setLast_Update(rs.getString("last_Updated"));
				listcriminal.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listcriminal;
	}
	//crime
	public static ArrayList getAllCrime(){
		ArrayList listcriminal = new ArrayList();
		String sql = "select * from criminal_reg";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				criminalModel model = new criminalModel();
				model.setId(rs.getInt("id"));
				model.setFirstName(rs.getString("firstName"));
				model.setLastName(rs.getString("lastName"));
				model.setBirth(rs.getString("birth"));
				model.setAddress(rs.getString("address"));
				model.setSex(rs.getInt("sex"));
				model.setImage(rs.getString("image"));
				model.setIduser(rs.getInt("iduser"));
				model.setLast_Update(rs.getString("last_Updated"));
				listcriminal.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listcriminal;
	}
	//search criminal
	public static ArrayList getSearchCrime(String name){
		ArrayList listcriminal = new ArrayList();
		String sql = "select * from criminal_reg where lastname like '%"+name+"%' or firstname like '%"+name+"%'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				criminalModel model = new criminalModel();
				model.setId(rs.getInt("id"));
				model.setFirstName(rs.getString("firstName"));
				model.setLastName(rs.getString("lastName"));
				model.setBirth(rs.getString("birth"));
				model.setAddress(rs.getString("address"));
				model.setSex(rs.getInt("sex"));
				model.setImage(rs.getString("image"));
				model.setIduser(rs.getInt("iduser"));
				model.setLast_Update(rs.getString("last_Updated"));
				listcriminal.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listcriminal;
	}
	//search prisoner
	public static ArrayList getSearchPrisoner(String name){
		ArrayList listcriminal = new ArrayList();
		String sql = "select cg.* from criminal_reg cg,CRIME_DETAIL cd where lastname like '%"+name+"%' or firstname like '%"+name+"%' and cg.id=cd.IDCRIMINAL and cd.STATUS_CRIME=1";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				criminalModel model = new criminalModel();
				model.setId(rs.getInt("id"));
				model.setFirstName(rs.getString("firstName"));
				model.setLastName(rs.getString("lastName"));
				model.setBirth(rs.getString("birth"));
				model.setAddress(rs.getString("address"));
				model.setSex(rs.getInt("sex"));
				model.setImage(rs.getString("image"));
				model.setIduser(rs.getInt("iduser"));
				model.setLast_Update(rs.getString("last_Updated"));
				listcriminal.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listcriminal;
	}
	//search most wanted
	public static ArrayList getSearchMostWanted(String name){
		ArrayList listcriminal = new ArrayList();
		String sql = "select cg.* from criminal_reg cg,CRIME_DETAIL cd where lastname like '%"+name+"%' or firstname like '%"+name+"%' and cg.id=cd.IDCRIMINAL and cd.STATUS_CRIME=2";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				criminalModel model = new criminalModel();
				model.setId(rs.getInt("id"));
				model.setFirstName(rs.getString("firstName"));
				model.setLastName(rs.getString("lastName"));
				model.setBirth(rs.getString("birth"));
				model.setAddress(rs.getString("address"));
				model.setSex(rs.getInt("sex"));
				model.setImage(rs.getString("image"));
				model.setIduser(rs.getInt("iduser"));
				model.setLast_Update(rs.getString("last_Updated"));
				listcriminal.add(model);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listcriminal;
	}
	
	//get name
	public static ArrayList getName(){
		ArrayList listcriminal = new ArrayList();
		String sql = "select * from criminal_reg";
		String a="";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				a=rs.getString("firstName")+" "+rs.getString("lastName");
				
				listcriminal.add(a);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return listcriminal;
	}
	
	
	public static criminalModel getbyid(String id){
		criminalModel model = null;
		String sql = "select*from criminal_reg where id =?";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ps.setString(1, "id");
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				model = new criminalModel();
				model.setId(rs.getInt("id"));
				model.setFirstName(rs.getString("firstName"));
				model.setLastName(rs.getString("lastName"));
				model.setBirth(rs.getString("birth"));
				model.setAddress(rs.getString("address"));
				model.setSex(rs.getInt("sex"));
				model.setImage(rs.getString("image"));
				model.setIduser(rs.getInt("iduser"));
				model.setLast_Update(rs.getString("last_Updated"));
				}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return model;
	}
	public static String convertID(int id){
		String convert = null;
		String sql = "select*from criminal_reg where id ='" +id+ "'";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				convert = Integer.toString(id);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return convert;
}

}
